package com.cpi.cornpp.salesforcast.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.salesforcast.dto.SalesForecastDTO;
import com.cpi.cornpp.salesforcast.exception.SalesForecastException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
/**
 * 
 * @description: This Class is used to handle the Sales Forecast Data Access
 * @author Edgar Joao
 */
public class SalesForecastDAO extends DBDAO{

	private static final String SELECT_SALES_FORECAST_BY_FILTER = "SELECT month, contracted, non_contracted, manual FROM (select distinct month from months_periods) as periods left join sap_sales_forecast on periods.month = sap_sales_forecast.period and customer = ? AND material = ? AND fiscal_year = ?";
	private static final String INSERT_SALES_FORECAST = "INSERT INTO sap_sales_forecast (customer, material, fiscal_year, period, contracted, non_contracted, manual) VALUES (?,?,?,?,?,?,?)";
	private static final String UPDATE_SALES_FORECAST = "UPDATE sap_sales_forecast SET contracted = ?, manual = ? WHERE customer = ? AND material = ? AND fiscal_year = ? AND period = ?";
	private static final String SELECT_BEFORE_INSERT_SALES_FORECAST = "SELECT count(*) FROM sap_sales_forecast WHERE customer = ? AND material = ? AND fiscal_year = ? AND period = ?";

	/**
	 * Obtains Sales Forecast List by fiscalYear, product and material
	 * @param searchForecastDTO
	 * @return
	 * @throws SalesForecastException
	 */
	public List getSalesForecast(SalesForecastDTO searchForecastDTO)throws SalesForecastException{
		List listOfSalesForecast = null;
		SalesForecastDTO dto = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		
		try{
			listOfSalesForecast = new ArrayList(0);
			preparedStatement = prepareSQL(SELECT_SALES_FORECAST_BY_FILTER, dsName);
			preparedStatement.setInt(1, searchForecastDTO.getCustomer().intValue());
			preparedStatement.setString(2, searchForecastDTO.getMaterial());
			preparedStatement.setInt(3, searchForecastDTO.getFiscalYear().intValue());
			rs = executeQuery(preparedStatement);
			while(rs.next()){
				dto = new SalesForecastDTO();
				dto.setCustomer(searchForecastDTO.getCustomer());
				dto.setMaterial(searchForecastDTO.getMaterial());
				dto.setFiscalYear(searchForecastDTO.getFiscalYear());
				dto.setPeriod(new Integer(rs.getInt(1)));
				dto.setContracted(new Double(rs.getDouble(2)));
				dto.setNonContracted(new Double(rs.getDouble(3)));
				dto.setManual(new Integer(rs.getInt(4)));
				listOfSalesForecast.add(dto);				
			}
		}catch (PoolException poolException){
			SalesForecastException salesForecastException = null;
			poolException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw salesForecastException;	
		} catch (SQLException sqlException) {
			SalesForecastException salesForecastException = null;
			sqlException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw salesForecastException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfSalesForecast;
	}
	/**
	 * Insert Sales Forecast
	 * @param dto
	 * @throws SalesForecastException
	 */
	public void insertSalesForecast(SalesForecastDTO dto) throws SalesForecastException{
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(INSERT_SALES_FORECAST, dsName);		
			preparedStatement.setInt(1, dto.getCustomer().intValue());			
			preparedStatement.setString(2, dto.getMaterial());			
			preparedStatement.setInt(3, dto.getFiscalYear().intValue());			
			preparedStatement.setInt(4, dto.getPeriod().intValue());			
			preparedStatement.setDouble(5, dto.getContracted().doubleValue());
			preparedStatement.setDouble(6, dto.getNonContracted().doubleValue());
			preparedStatement.setInt(7, new Integer(1).intValue());
			executeInsert(preparedStatement);						
		}catch (PoolException poolException){
			SalesForecastException salesForecastException = null;
			poolException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw salesForecastException;	
		} catch (SQLException sqlException) {
			SalesForecastException salesForecastException = null;
			sqlException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw salesForecastException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	/**
	 * Update Sales Forecast
	 * @param dto
	 * @throws SalesForecastException
	 */
	public void updateSalesForecast(SalesForecastDTO dto) throws SalesForecastException{
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(UPDATE_SALES_FORECAST, dsName);		
			preparedStatement.setDouble(1, dto.getContracted().doubleValue());
			preparedStatement.setInt(2, new Integer(1).intValue());
			preparedStatement.setInt(3, dto.getCustomer().intValue());						
			preparedStatement.setString(4, dto.getMaterial());
			preparedStatement.setInt(5, dto.getFiscalYear().intValue());
			preparedStatement.setInt(6, dto.getPeriod().intValue());
			executeInsert(preparedStatement);						
		}catch (PoolException poolException){
			SalesForecastException salesForecastException = null;
			poolException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw salesForecastException;	
		} catch (SQLException sqlException) {
			SalesForecastException salesForecastException = null;
			sqlException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw salesForecastException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	/**
	 * Verify if current Sales Forecast exist
	 * @param dto
	 * @return
	 * @throws SalesForecastException
	 */
	public boolean existSalesForecast(SalesForecastDTO dto) throws SalesForecastException{		
		PreparedStatement preparedStatement =  null;
		boolean result = false;		
		try {
			preparedStatement =  prepareSQL(SELECT_BEFORE_INSERT_SALES_FORECAST, dsName);						
			preparedStatement.setInt(1, dto.getCustomer().intValue());						
			preparedStatement.setString(2, dto.getMaterial());
			preparedStatement.setInt(3, dto.getFiscalYear().intValue());
			preparedStatement.setInt(4, dto.getPeriod().intValue());
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {
				result=rst.getBoolean(1);
			}
		}catch (PoolException poolException){
			SalesForecastException salesForecastException = null;
			poolException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw salesForecastException;		
		}catch (SQLException sqlException){
			SalesForecastException salesForecastException = null;
			sqlException.printStackTrace(System.out);
			salesForecastException = new SalesForecastException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw salesForecastException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;		
	}
}
